package com.stores.v1.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.stores.v1.entity.Manufacturer;
import com.stores.v1.toolset.DatabaseInitializationException;

public class ManufacturerModel extends BaseModel {

	/***
	 * 
	 * @param manufacturer
	 * @return
	 * @throws DatabaseInitializationException
	 * @throws DatabaseCommandException
	 */
	public int addNew(Manufacturer manufacturer)
			throws DatabaseInitializationException, DatabaseCommandException {
		Connection connection = this.getDatabase().getOpenConnection();
		StringBuilder sql = new StringBuilder();
		int identityKey = 0;
		sql.append("insert into manufacturer(name,tagline,smallLogoUrl,mediumLogoUrl,largeLogoUrl) values(?,?,?,?,?);");
		try {
			PreparedStatement command = connection.prepareStatement(
					sql.toString(), Statement.RETURN_GENERATED_KEYS);
			command.setString(1, manufacturer.getName());
			command.setString(2, manufacturer.getTagline());
			command.setString(3, manufacturer.getSmallLogoUrl());
			command.setString(4, manufacturer.getMediumLogoUrl());
			command.setString(5, manufacturer.getLargeLogoUrl());
			command.executeUpdate();
			ResultSet generatedKeys = command.getGeneratedKeys();
			if (generatedKeys.next()) {
				identityKey = generatedKeys.getInt(1);
			}
			command.close();
			this.getDatabase().closeConnection(connection);
		} catch (SQLException e) {
			throw new DatabaseCommandException(String.format(
					"Failed to insert into [manufacturer] due to [%s]",
					e.getMessage()));
		}
		return identityKey;
	}

	/***
	 * 
	 * @param manufacturer
	 * @return
	 * @throws DatabaseInitializationException
	 * @throws DatabaseCommandException
	 */
	public int update(Manufacturer manufacturer)
			throws DatabaseInitializationException, DatabaseCommandException {
		Connection connection = this.getDatabase().getOpenConnection();
		int recordsAffected = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("update manufacturer set name=?, tagline=?, smallLogoUrl=?, mediumLogoUrl=?, largeLogoUrl=? where mfgId=?");
		try {
			PreparedStatement command = connection.prepareStatement(sql
					.toString());
			command.setString(1, manufacturer.getName());
			command.setString(2, manufacturer.getTagline());
			command.setString(3, manufacturer.getSmallLogoUrl());
			command.setString(4, manufacturer.getMediumLogoUrl());
			command.setString(5, manufacturer.getLargeLogoUrl());
			command.setShort(6, manufacturer.getId());
			recordsAffected = command.executeUpdate();
			this.getDatabase().closeConnection(connection);
		} catch (Exception e) {
			throw new DatabaseCommandException(String.format(
					"Failed to update id=[%d] from [manufacturer] due to [%s]",
					manufacturer.getId(), e.getMessage()));
		}
		return recordsAffected;
	}

	/**
	 * 
	 * @param option
	 * @throws DatabaseInitializationException
	 * @throws DatabaseCommandException
	 */
	public List<Manufacturer> retrieve(FilterOption option)
			throws DatabaseInitializationException, DatabaseCommandException {
		StringBuilder sql = new StringBuilder();

		sql.append("select mfgId,name,tagline,smallLogoUrl,mediumLogoUrl,largeLogoUrl from manufacturer ");

		if (option.getWhereClause() != null
				&& option.getWhereClause().length() > 0) {
			sql.append("where " + option.getWhereClause() + " ");
		}
		if (option.isOrderByDescending()
				&& option.getOrderByDescendingColumn() != null) {
			sql.append("order by " + option.getOrderByDescendingColumn()
					+ " desc ");
		}

		if (option.getTake() > 0) {
			sql.append(String.format("limit %d,%d ", option.getSkip(),
					option.getTake()));
		} else {
			sql.append("limit 0,50 ");
		}

		Connection connection = this.getDatabase().getOpenConnection();
		List<Manufacturer> manufacturers = new ArrayList<Manufacturer>();
		try {
			PreparedStatement command = connection.prepareStatement(sql
					.toString());
			ResultSet rows = command.executeQuery();
			while (rows.next()) {
				Manufacturer mfg = new Manufacturer();
				mfg.setId(rows.getShort("mfgId"));
				mfg.setName(rows.getString("name"));
				mfg.setTagline(rows.getString("tagline"));
				mfg.setSmallLogoUrl(rows.getString("smallLogoUrl"));
				mfg.setMediumLogoUrl(rows.getString("mediumLogoUrl"));
				mfg.setLargeLogoUrl(rows.getString("largeLogoUrl"));
				manufacturers.add(mfg);
			}
			this.getDatabase().closeConnection(connection);
		} catch (Exception e) {
			throw new DatabaseCommandException(String.format(
					"Failed to select from [manufacturer] due to [%s]",
					e.getMessage()));
		}
		return manufacturers;
	}

	/***
	 * 
	 * @param manufacturer
	 * @return
	 * @throws DatabaseInitializationException
	 * @throws DatabaseCommandException
	 */
	public int delete(Manufacturer manufacturer)
			throws DatabaseInitializationException, DatabaseCommandException {
		Connection connection = this.getDatabase().getOpenConnection();
		int recordsAffected = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("delete from manufacturer where mfgId=?");
		try {
			PreparedStatement command = connection.prepareStatement(sql
					.toString());
			command.setShort(1, manufacturer.getId());
			recordsAffected = command.executeUpdate();
			this.getDatabase().closeConnection(connection);
		} catch (Exception e) {
			throw new DatabaseCommandException(String.format(
					"Failed to delete id=[%d] from [manufacturer] due to [%s]",
					manufacturer.getId(), e.getMessage()));
		}
		return recordsAffected;
	}
}
